-- @owner: songjing20
-- @date: 2024-7-12
-- @testpoint: connect by子句,prior关键字后+表达式

--step1:创建普通表，插入数据;expect:建表成功，插入数据成功
drop table if exists t_connectby_0022 cascade;
create table t_connectby_0022(name varchar, id int, fatherid int);
insert into t_connectby_0022 values('A', 1, 0), ('B', 2, 1),('C',3,1),('D',4,1),('E',5,2),('F',0,1),('G',1,2);

--step2:prior关键字后+表达式，查询id=1作为初始集合的递归查询;expect:成功
select * from t_connectby_0022 start with id=1 connect by nocycle prior id*2 >= fatherid limit 10;
select * from t_connectby_0022 start with id=1 connect by nocycle prior id+fatherid >= fatherid limit 10;
select * from t_connectby_0022 start with id=1 connect by nocycle prior id*2 = fatherid limit 10;
select * from t_connectby_0022 start with id=1 connect by nocycle id < prior fatherid*2 limit 5;
select * from t_connectby_0022 start with id=1 connect by nocycle prior id >= prior id+fatherid limit 5;
select * from t_connectby_0022 start with id=1 connect by nocycle prior id = prior fatherid-id limit 5;

--step3:清理环境;expect:成功
drop table t_connectby_0022 cascade;
